package com.fbinggun.sql;

import java.sql.*;

/**
 * 数据库表
 */
public  class Table {
    public SQLite getSql() {
        return mySql;
    }

    public  String getTablename() {
        return tablename;
    }
    static  ResultSet s;
    SQLite mySql;
    String tablename;

    //构建方法
    //SQL数据库连接
    //tablename 表名
    public Table(SQLite mySql, String tablename) throws SQLException, ClassNotFoundException {
        this.mySql = mySql;
        this.tablename = tablename;
        if(!IsTableNull()){
         //  sql.plugin.getLogger().warning("数据表不存在 请检查数据库");
        }
    }
    //判断表是否存在
    //存在返回true
    public boolean IsTableNull() throws SQLException, ClassNotFoundException {
        if(mySql.IsConnection()){
            mySql.connection();
        }
        String databaseName = mySql.getConnection().getCatalog();
        DatabaseMetaData data =mySql.getConnection().getMetaData();
        ResultSet s =data.getTables(databaseName, null, tablename, null);
        if (s.next()) {

            return true;
        }else {
            return false;
        }
    }









//    public boolean IsTableNull() throws SQLException {
//        String query = "SELECT 1 FROM " + tablename + " LIMIT 1";
//        try (Connection connection = mySql.getConnection();
//             Statement statement = connection.createStatement();
//             ResultSet rs = statement.executeQuery(query)) {
//            // 如果查询成功执行并返回结果，则表示表存在
//            return true;
//        } catch (SQLException e) {
//            // 如果抛出异常，表示表不存在
//            return false;
//        }
//    }
    //创建表
    //Formula 创建公式
    public void CreateTable(Formula... Formula) throws SQLException, ClassNotFoundException {
        if(!IsTableNull()) {
            String x = "(";
            for(Formula f:Formula){
                String a= f.toString();
                if(Formula[Formula.length-1].equals(f)){
                    x=x+a+")";
                }else {
                x=x+a+",";}
            }
            String txt = "create table `"+tablename+"` "+ x;
        Statement statement=    mySql.getStatement();
        statement.executeUpdate(txt);
        statement.close();

        }
    }
    //设置索引
    public void SetUniqueIndex(String index) throws SQLException {
        String t ="CREATE UNIQUE INDEX id ON "+tablename+" (id) ";
        Statement statement=    mySql.getStatement();
        statement.executeUpdate(t);
        statement.close();
    }




    public synchronized boolean IsColumnNull(String Column){

        return true;
    }
    //判断索引值是否存在
    //index 索引列
    //Conditions 搜索值
    public boolean IsIndex(String index,String Conditions) throws SQLException, ClassNotFoundException {
        String t = "select * from "+tablename+" where "+index+"='"+Conditions+"'";
        if(mySql.IsConnection()){
            mySql.connection();
        }
        Statement statement=    mySql.getStatement();
        ResultSet s = statement.executeQuery(t);
        if (s.next()) {
            s.close();
            statement.close();
            return true;
        }else {
            s.close();
            statement.close();
            return false;
        }
    }
    //判断此列是否存在数据
    //index 索引列
    //Conditions 搜索值
    //Column 查询列
    public boolean IsIndex(String index,String Conditions,String Column) throws SQLException, ClassNotFoundException {
        String t = "select * from "+tablename+" where "+index+"='"+Conditions+"'";
        if(mySql.IsConnection()){
            mySql.connection();
        }
        Statement statement=    mySql.getStatement();
        ResultSet  s = statement.executeQuery(t);
        if (s.next()) {
            if(s.getObject(Column)!=null){
                s.close();
                statement.close();

                return true;
            }
        }
        s.close();
        statement.close();
        return false;
    }


    //获得值
    //index 索引列
    //Conditions 搜索值
    //Column 搜索列
    public  Object getObject(String index, String Conditions, String Column) throws SQLException, ClassNotFoundException {
        String t = "select * from "+tablename+" where "+index+"='"+Conditions+"'";
        if(mySql.IsConnection()){
            mySql.connection();
        }
        Statement statement=    mySql.getStatement();
        ResultSet s =statement.executeQuery(t);
        if(s.next()){
            Object e =s.getObject(Column);
            s.close();
            statement.close();
          return  e;
        }else {
            s.close();
            statement.close();
            return null;
        }
    }
    //设置值
    //index 索引列
    //Conditions 搜索值
    //Column 搜索列
    //Value 值
    public  void setObject(String index, String Conditions, String Column, Object Value) throws SQLException, ClassNotFoundException {
        if(mySql.IsConnection()){
            mySql.connection();
        }
        if(IsIndex(index,Conditions)){
            Statement statement=    mySql.getStatement();
            String t = "select * from "+tablename+" where "+index+"='"+Conditions+"'";
            ResultSet  s = statement.executeQuery(t);
            if(s.next()){
                String x ="UPDATE "+tablename+" SET `"+Column+"` = '"+Value+"' WHERE "+index+" = '"+Conditions+"'";
                mySql.getStatement().executeUpdate(x);
                s.close();
                statement.close();
            }
        }else {
            insertObject(index,Conditions,new ColValue(Column,Value));
        }
    }
    //插入列 (只插入索引值)
    //index 索引列
    //Conditions 搜索值
    public  void insertObject(String index, String Conditions) throws SQLException, ClassNotFoundException {
        if(mySql.IsConnection()){
            mySql.connection();
        }
        if(!IsIndex(index,Conditions)){
            String t ="insert into "+tablename+" ("+index+") values('"+Conditions+"')";
            Statement statement=    mySql.getStatement();
            statement.executeUpdate(t);
            statement.close();

        }
    }
    //插入列 (只插入索引值)
    //index 索引列
    //Conditions 搜索值
    public  void insertObject(String index, String Conditions,ColValue... ColValue) throws SQLException, ClassNotFoundException {
        if(mySql.IsConnection()){
            mySql.connection();
        }
       String col=index;
        Object Value = "'"+Conditions+"'";
       for(ColValue x:ColValue){
           col=col+","+x.Col;
           Value= Value +",'"+x.Value+"'";
       }
        if(!IsIndex(index,Conditions)){
            String e ="insert into "+tablename+" ("+col+") values("+Value+")";
            Statement statement=    mySql.getStatement();
            statement.executeUpdate(e);
            statement.close();
        }

    }
    //清空表
    public  void clearTable() throws SQLException, ClassNotFoundException {
        if(mySql.IsConnection()){
            mySql.connection();
        }
        String t ="truncate table "+tablename;
        Statement statement=    mySql.getStatement();
        statement.executeUpdate(t);
        statement.close();
    }
    public void del(String index, String id) throws SQLException, ClassNotFoundException {
        if (this.IsIndex(index, id)) {
            String str = "DELETE FROM " + this.getTablename() + " WHERE " + index + " = '" + id + "'";
            this.getSql().getStatement().executeUpdate(str);
        }

    }
}


